package yuhuibear.poat.object.mapEntity;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import yuhuibear.poat.annotations.relation.Field;
import yuhuibear.poat.annotations.relation.Table;
import yuhuibear.poat.annotations.relation.View;
import yuhuibear.poat.exception.DuplicateDefine;
import yuhuibear.poat.exception.MissAttributesOfBean;
import yuhuibear.poat.exception.PoatException;
import yuhuibear.poat.macro.MacroDefine;
import yuhuibear.poat.object.bean.AttributeMapper;
import yuhuibear.poat.object.bean.BeanStruct;
import yuhuibear.poat.util.DataBaseTool;

/**
 * 
 * @author yuhuibear
 */
public final class DataBaseMapper {

	private static Log log = LogFactory.getLog(DataBaseMapper.class);

	private final static String tempSchema = "tmp";

	private final static String schema = "public";

	private Map<Class<?>, BeanStruct> beans;

	public DataBaseMapper(Map<Class<?>, BeanStruct> beanMap) {
		beans = beanMap;
	}

	public int createTable(Connection con, boolean clear) throws Exception {
		int cnt = 0;
		try {
			con.setAutoCommit(false);
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			if (!clear) {
				cnt = mapEntity(con, false);
			}
			else {
				try {
					con.createStatement().execute("create schema " + tempSchema);
				} catch (Exception e) {
					con.rollback();
					if (e.getMessage().contains("schema")) {
						con.createStatement().execute("drop schema "
								+ tempSchema + " cascade ");
						con.createStatement().execute("create schema "
								+ tempSchema);
					}
					else {
						throw e;
					}
				}
				con.createStatement().execute("grant all on schema "
						+ tempSchema + " to public ");
				con.createStatement().execute("set search_path = tmp");
				cnt = mapEntity(con, true);
				con.createStatement().execute("drop schema public cascade");
				con.createStatement().execute("alter schema " + tempSchema
						+ " rename to public");
				con.createStatement().execute("set search_path = public");
				log.info("replaced old public schema.");
			}
			con.commit();
		} catch (Exception e) {
			log.info("fail to create new schema, rollback, now...");
			con.rollback();
			log.info("rollback, done.");
			throw e;
		} finally {
			con.close();
		}
		return cnt;
	}

	private int updateColumns(BeanStruct beanstruct, Connection con)
			throws SQLException {
		int cnt = 0;
		Table table = beanstruct.getAnnotation(Table.class);
		Map<String, AttributeMapper> definedAttrs = beanstruct.getAttributesMapped();
		Set<String> existsAtrs = listColumnsFromDB(table.name(), con);
		// remove exists column.
		Set<String> oldAtr = new HashSet<String>(existsAtrs);
		oldAtr.removeAll(definedAttrs.keySet());
		if (oldAtr.size() == 0)
			return cnt;
		for (String col : oldAtr) {
			removeColumnFromDB(table.name(), col, con);
			cnt++;
			log.info(String.format("drop column : %1$s from table : %2$s ", col, table.name()));
		}
		// insert new column.
		oldAtr.clear();
		Set<String> newAtr = new HashSet<String>(definedAttrs.keySet());
		newAtr.removeAll(existsAtrs);
		for (String col : newAtr) {
			Field field = definedAttrs.get(col).getAnnotation(Field.class);
			if (field == null) {
				continue;
			}
			addColumnToDB(table.name(), field, con);
			cnt++;
			log.info(String.format("add column : %1$s to table : %2$s ", col, table.name()));
		}
		return cnt;
	}

	/**
	 * 鍦ㄦ寚瀹氱殑鏁版嵁婧愪笂鍒涘缓瀹炰綋鏄犲皠鐨勮〃銆� *
	 * 
	 * @param source
	 * @return
	 * @throws SQLException
	 * @throws MissAttributesOfBean
	 * @throws Exception
	 * @throws java.lang.Exception
	 */
	private int mapEntity(Connection con, boolean toClear) throws SQLException,
			PoatException, MissAttributesOfBean {

		Set<String> existsTables = DataBaseTool.listExistTables(con, (toClear
																				? tempSchema
																				: schema), new String[] { "TABLE" });
		Map<String, SqlTask> tasks = creatTask(con, toClear, existsTables);
		return executeTasks(tasks, con);
	}

	private Map<String, SqlTask> creatTask(Connection con, boolean toClear,
			Set<String> existsTables) throws SQLException, DuplicateDefine {

		Map<String, SqlTask> tasks = new HashMap<String, SqlTask>();
		for (Entry<Class<?>, BeanStruct> entry : beans.entrySet()) {
			BeanStruct beanstruct = entry.getValue();
			View view = beanstruct.getAnnotation(View.class);
			if (view != null) {
				if (tasks.containsKey(view.name())) {
					SqlTask st = tasks.get(view.name());
					throw new DuplicateDefine(view.name(), st.getDefinePlace(), beanstruct.getType().toString());
				}

				tasks.put(view.name(), new ViewTask(beanstruct, view));
			}
			else {
				Table table = beanstruct.getAnnotation(Table.class);
				if (table == null)
					continue;
				if (tasks.containsKey(table.name())) {
					SqlTask st = tasks.get(table.name());
					throw new DuplicateDefine(table.name(), st.getDefinePlace(), beanstruct.getType().toString());
				}
				if (!toClear && existsTables.contains(table.name())) {
					updateColumns(beanstruct, con);
				}
				else {
					tasks.put(table.name(), new TableTask(beanstruct));
				}
			}
			Map<String, MacroDefine> macros = beanstruct.getMacros();
			if (macros != null && macros.size() > 0) {
				for (Map.Entry<String, MacroDefine> mac : macros.entrySet()) {
					String name = mac.getKey();
					MacroDefine md = mac.getValue();
					if (tasks.containsKey(name)) {
						SqlTask st = tasks.get(name);
						throw new DuplicateDefine(name, md.getDefPlace(), st.getDefinePlace());
					}
					tasks.put(mac.getKey(), new MacroTask(mac.getValue(), beanstruct));
				}
			}
		}// for build task.
		log.info(String.format("Found %d tasks, %s exists, include: %d entities, %d views, %d macros, excuting...", tasks.size(), (toClear
																																			? "clear"
																																			: "do not clear"), TableTask.tableCount(), ViewTask.viewCount(), MacroTask.macroCount()));
		return tasks;
	}

	private int executeTasks(Map<String, SqlTask> tasks, Connection con)
			throws MissAttributesOfBean, PoatException, SQLException {
		int tCnt = SqlTask.getTotalCnt();
		int tableCnt = TableTask.tableCount();
		int viewCnt = ViewTask.viewCount();
		int macroCnt = MacroTask.macroCount();

		try {
			for (MapEntityTask.Type curType : new MapEntityTask.Type[] {
					MapEntityTask.Type.Table,
					MapEntityTask.Type.View,
					MapEntityTask.Type.Macro })
				for (Map.Entry<String, SqlTask> t : tasks.entrySet())
					if (t.getValue().getType().equals(curType))
						doTask(t.getValue(), tasks, con);
		} catch (PoatException ex) {
			List<String> toDel = new LinkedList<String>();
			for (Map.Entry<String, SqlTask> t : tasks.entrySet())
				if (!t.getValue().isDone())
					toDel.add(t.getKey());
			throw new PoatException(String.format("remain tasks %d : \n %s", tasks.size(), toDel.toString()), ex);
		} finally {
			StringBuffer sb = new StringBuffer();
			if (SqlTask.getTotalCnt() > 0) {
				sb.append("failed " + SqlTask.getTotalCnt() + " tasks:\n");
				int errorCnt = 0;
				for (Map.Entry<String, SqlTask> t : tasks.entrySet()) {
					SqlTask tk = t.getValue();
					if (tk.isDone())
						continue;
					sb.append(String.format("\t[%d]%s:, name: %s, type: %s, \n\t\ter: %s, \n\t\tsql: %s\n", errorCnt++, tk.getDefinePlace(), tk.getName(), tk.getType(), tk.getError(), tk.getSql()));
				}
			}
			else sb.append(String.format("Congratulations!!! Successfully "));
			sb.append(String.format("Excuted %d/%d, %d/%d entities, %d/%d views, %d/%d macros.", tCnt
					- SqlTask.getTotalCnt(), tCnt, tableCnt
					- TableTask.tableCount(), tableCnt, viewCnt
					- ViewTask.viewCount(), viewCnt, macroCnt
					- MacroTask.macroCount(), macroCnt));
			log.info(sb);
		}
		return tCnt - SqlTask.getTotalCnt();
	}

	private void doTask(SqlTask task, Map<String, SqlTask> taskList,
			Connection con) throws PoatException, SQLException {
		if (task == null || task.isDone())
			return;
		Set<String> refs = task.getReferences();
		if (refs.size() > 0)
			for (String ref : refs) {
				doTask(taskList.get(ref), taskList, con);
			}
		Savepoint sp = con.setSavepoint();
		try {
			con.createStatement().execute(task.getSql());
			task.done();
			if (task.getType().equals(MapEntityTask.Type.View))
				checkViewConsistency(task.getStruct(), con);

			log.debug(String.format("[%d]done: %s.", SqlTask.getTotalCnt(), task.getName()));
		} catch (SQLException e) {
			con.rollback(sp);
			if (task.getType().equals(MapEntityTask.Type.Macro))
				task.setError(e.getMessage());
			else throw new PoatException(String.format("[%d]fail at %s: \n\tname: %s, type: %s, refs: %s, \n\tsql: %s.", SqlTask.getTotalCnt(), task.getDefinePlace(), task.getName(), task.getType(), task.getReferences(), task.getSql()), e);
		} finally {
			con.releaseSavepoint(sp);
		}
	}

	private Set<String> listColumnsFromDB(String tableName, Connection con)
			throws SQLException {
		Set<String> cols = new HashSet<String>();
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from " + tableName
				+ " offset 0 limit 1");
		ResultSetMetaData rsmd = rs.getMetaData();
		for (int i = 1; i <= rsmd.getColumnCount(); i++) {
			cols.add(rsmd.getColumnName(i));
		}
		return cols;
	}

	private boolean removeColumnFromDB(String table, String col, Connection con)
			throws SQLException {
		String sql = "ALTER TABLE " + table + " DROP COLUMN " + col;
		return con.createStatement().execute(sql);
	}

	private boolean addColumnToDB(String table, Field col, Connection con)
			throws SQLException {
		String sql = "ALTER TABLE " + table + " ADD COLUMN " + col.name() + " "
				+ col.type() + " " + col.constrain();
		return con.createStatement().execute(sql);
	}

	/**
	 * Check consistency of views.
	 * 
	 * @param atrsOfbean
	 * @param view
	 * @param con
	 * @return
	 * @throws MissAttributesOfBean
	 * @throws SQLException
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	private boolean checkViewConsistency(BeanStruct bean, Connection con)
			throws MissAttributesOfBean, SQLException {
		ResultSet rs = con.createStatement().executeQuery("select * from "
				+ bean.getDBName());
		ResultSetMetaData md = rs.getMetaData();
		Set<String> names = new HashSet<String>();
		int cmax = md.getColumnCount();
		for (int i = 1; i <= cmax; i++) {
			names.add(md.getColumnName(i));
		}
		Set<String> atrs = bean.getAttributes().keySet();
		Set names_cp = new HashSet();
		names_cp.addAll(Arrays.asList(names.toArray().clone()));
		names_cp.removeAll(atrs);
		if (names_cp.size() > 0) {
			atrs.removeAll(names);
			throw new MissAttributesOfBean(bean.getType(), atrs, names_cp);
		}
		return true;
	}

	@SuppressWarnings("unused")
	private void removeExistView(List<View> lst, Connection con)
			throws SQLException {
		for (int i = 0; i < lst.size(); i++) {
			View v = lst.get(i);
			if (v == null)
				continue;
			try {
				con.createStatement().execute("drop view " + v.name());
				lst.remove(v);
				log.info(String.format("drop view : %1$s ", v.name()));

			} catch (SQLException e) {
				if (lst.size() < 1)
					throw e;
				lst.remove(v);
				removeExistView(lst, con);
			}
		}
	}
}
